﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using Models;

namespace DAL
{
    public class CircleOfFriendsService
    {

        public int HaveTab( string TabNam ) {
            string sql = string.Format("select count(1) from CircleOfFriends{0} ", TabNam);
            try {
                SqlConnection conn = ConnPoll.GetConn();
                return Convert.ToInt32(SQLHelper.ExecuteScalar(conn, System.Data.CommandType.Text, sql));
            } catch (Exception) {
                return -1;
            }
        }


        //public bool SearchTable(string UserName)
        //{
        //    SqlConnection conn = ConnPoll.GetConn();
        //    bool tempbool = false;
        //    try {
        //        string sql = "select * from CircleOfFriends" + UserName;
        //        ConnPoll.Open();
        //        SqlCommand com = new SqlCommand(sql, conn);
        //        SqlDataReader dr = com.ExecuteReader();
        //        while (dr.Read()) {
        //            tempbool = true;
        //            break;
        //        }
        //        dr.Close();
        //        com = null;
        //        ConnPoll.Close();
        //    }
        //    catch (Exception) {
        //        return false;
        //    }
        //    return tempbool;
        //}

        #region 创建朋友圈分析表
        /// <summary>
        /// 创建朋友圈分析表
        /// </summary>
        /// <param name="UserName">用户名</param>
        /// <returns></returns>
        public void CreateCircleOfFriends(string UserName)
        {
            SqlConnection conn = ConnPoll.GetConn();
            try
            {
                ConnPoll.Open();
                string tbna = " CircleOfFriends" + UserName;
                string sql = " use bds249611391_db"
                    + " create table " + tbna
                    + " ("
                    + " COFId int not null identity(1,1),"//主键Id
                    + " FriendName nvarchar(200) not null,"//好友昵称
                    + " COFLike nvarchar(200) not null,"//朋友圈喜好
                    + " ActiveTime datetime not null,"//活跃时间
                    + " PPAnalysis nvarchar(500) not null,"//个人画像分析PersonalPortraitAnalysis
                    + " Custom1 nvarchar(2000) null,"//自定义项
                    + " Custom2 nvarchar(2000) null,"//自定义项
                    + " Custom3 nvarchar(2000) null"//自定义项
                    + " )"
                    + " use bds249611391_db"
                    + " alter table " + tbna
                    + " add constraint PK_COFId primary key (COFId)";
                SqlCommand com = new SqlCommand(sql, conn);
                com.ExecuteNonQuery();
                com = null;
                ConnPoll.Close();
            }
            catch (Exception)
            {

            }
        }
        #endregion

        #region 新增信息
        /// <summary>
        /// 新增信息
        /// </summary>
        /// <param name="cof"></param>
        /// <param name="UserName"></param>
        /// <returns></returns>
        public int Insert(CircleOfFriends cof, string UserName)
        {
            SqlConnection conn = ConnPoll.GetConn();
            string sql = "insert into CircleOfFriends" + UserName + " (FriendName,COFLike,ActiveTime,PPAnalysis,Custom1,Custom2,Custom3)"
                + " values (@FriendName,@COFLike,@ActiveTime,@PPAnalysis,@Custom1,@Custom2,@Custom3)";
            SqlParameter[] pars = new SqlParameter[]
            {
                new SqlParameter("@FriendName",cof.FriendName),
                new SqlParameter("@COFLike",cof.COFLike),
                new SqlParameter("@ActiveTime",cof.ActiveTime),
                new SqlParameter("@PPAnalysis",cof.PPAnalysis),
                new SqlParameter("@Custom1",cof.Custom1),
                new SqlParameter("@Custom2",cof.Custom2),
                new SqlParameter("@Custom3",cof.Custom3)
            };
            return SQLHelper.ExecuteNonQuery(conn, System.Data.CommandType.Text, sql, pars);
        }
        #endregion

        #region 删除信息
        public int Delete(int i, string UserName)
        {
            SqlConnection conn = ConnPoll.GetConn();
            string sql = "delete CircleOfFriends" + UserName + " where COFId=@COFId";
            return SQLHelper.ExecuteNonQuery(conn, System.Data.CommandType.Text, sql, new SqlParameter("@COFId", i));
        }
        #endregion

        #region 修改信息
        public int Set(CircleOfFriends cof, string UserName,string FriendName)
        {
            SqlConnection conn = ConnPoll.GetConn();
            string sql = " update CircleOfFriends" + UserName
                + " set COFLike=@COFLike,"
                + " ActiveTime=@ActiveTime,"
                + " PPAnalysis=@PPAnalysis,"
                + " Custom1=@Custom1,"
                + " Custom2=@Custom2,"
                + " Custom3=@Custom3"
                + " where FriendName=@FriendName";
            SqlParameter[] pars = new SqlParameter[]
            {
                new SqlParameter("@COFLike",cof.COFLike),
                new SqlParameter("@ActiveTime",cof.ActiveTime),
                new SqlParameter("@PPAnalysis",cof.PPAnalysis),
                new SqlParameter("@Custom1",cof.Custom1),
                new SqlParameter("@Custom2",cof.Custom2),
                new SqlParameter("@Custom3",cof.Custom3),
                new SqlParameter("@FriendName",FriendName)
            };
            return SQLHelper.ExecuteNonQuery(conn, System.Data.CommandType.Text, sql,pars);
        }
        #endregion

        #region 查询信息
        /// <summary>
        /// 查询信息
        /// </summary>
        /// <param name="cof"></param>
        /// <param name="UserName"></param>
        /// <returns></returns>
        public int SearchAll( string UserName,string FriendName)
        {
            SqlConnection conn = ConnPoll.GetConn();
            string sql = "select count(1) from CircleOfFriends"+ UserName +" where FriendName = '" + FriendName+"'";
            try
            {
                return Convert.ToInt32(SQLHelper.ExecuteScalar(conn,System.Data.CommandType.Text,sql));
            }
            catch (Exception)
            {
                return -1;
                throw;
            }
        }
        #endregion


        public CircleOfFriends Show(string UserName, string FriendName)
        {
            SqlConnection conn = ConnPoll.GetConn();
            string sql = string.Format("select * from CircleOfFriends{0} where FriendName = '{1}'", UserName, FriendName);
            SqlDataReader dr = SQLHelper.ExecuteReader(conn,System.Data.CommandType.Text,sql);
            CircleOfFriends cof = new CircleOfFriends();
            while (dr.Read())
            {
                cof.COFLike = dr["COFLike"].ToString().Trim();
                cof.ActiveTime = Convert.ToDateTime(dr["ActiveTime"]);
                cof.PPAnalysis = dr["PPAnalysis"].ToString().Trim();
                cof.Custom1 = dr["Custom1"].ToString().Trim();
                cof.Custom2 = dr["Custom2"].ToString().Trim();
                cof.Custom3 = dr["Custom3"].ToString().Trim();
            }
            dr.Close();
            return cof;
        }

        #region 查找库中是否已有此内容
        /// <summary>
        /// 查找库中是否已有此内容
        /// </summary>
        /// <param name="cof"></param>
        /// <param name="UserName">用户名</param>
        /// <returns></returns>
        public bool Search( string FriendName, string UserName ) {
            SqlConnection conn = ConnPoll.GetConn();
            string sql = "select count(1) from CircleOfFriends" + UserName + " where FriendName=@FriendName";
            return Convert.ToInt32(SQLHelper.ExecuteScalar(conn, System.Data.CommandType.Text, sql, new SqlParameter("@FriendName", FriendName))) == 0 ? false : true;
        }
        #endregion
    }
}
